package com.project;

import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.widget.Toast;


public class DBHelper extends SQLiteOpenHelper{

	static final String dbName="PIN";
	static final String pinInfoTable="pininfo";
	static final String colID="id";
	static final String colName="name";
	static final String colLat="lat";
	static final String colLng="lng";
	static final String colDescription="description";
	static final String colPicture="picture";
	
	Cursor cur;

	
	public DBHelper(Context context) {
		super(context, dbName, null, 33);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL("create table pininfo (id integer primary key autoincrement, " +
				"name text not null , lat numeric, lng numeric, description text not null, picture text not null)");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS "+pinInfoTable);
		onCreate(db);
	}

	boolean CheckDB(){
		boolean empty = false;
		SQLiteDatabase db = this.getReadableDatabase();
		cur = db.rawQuery("SELECT * FROM "+pinInfoTable+" ORDER BY id LIMIT 0,1", null);
		int count = cur.getCount();
		if(count >= 1){
			empty = true;
		}
		return empty;
	}
	
	int countRow(){
		boolean empty = false;
		SQLiteDatabase db = this.getReadableDatabase();
		cur = db.rawQuery("SELECT * FROM "+pinInfoTable+" ORDER BY id", null);
		int count = cur.getCount();
		
		return count;
	}
	
	public void AddPin(String name, double lat, double lng, String description, String picture){
		
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues cv = new ContentValues();
		
		cv.put(colName, name);	
		cv.put(colLat, lat);	
		cv.put(colLng, lng);	
		cv.put(colDescription, description);	
		cv.put(colPicture, picture);	
		
		db.insert(pinInfoTable, null, cv);
		db.close();
	}	
	
	public Cursor allPin(){
		SQLiteDatabase db = this.getReadableDatabase();
		String[] col = new String[]{colName, colID, colLat, colLng}; 
		cur = db.query(pinInfoTable, col, null, null, null, null, colName);
		return cur;
	}
	
	public Cursor showPin(int id){
		SQLiteDatabase db = this.getReadableDatabase();
		String where = colID+" = '"+id+"'";
		String[] col = new String[]{colName, colID, colLat, colLng, colDescription, colPicture}; 
		cur = db.query(pinInfoTable, col, where, null, null, null, null);
		return cur;
	}
	
	public Cursor lastInsert(){
		SQLiteDatabase db = this.getReadableDatabase();
		cur = db.rawQuery("SELECT * FROM "+pinInfoTable+" ORDER BY id DESC LIMIT 0,1", null);
		return cur;
	}
}